package com.ssy.lingxi.report.dao.logistics;

import com.ssy.lingxi.report.entity.settleaccount.MemberLogisticsSettlementDO;
import com.ssy.lingxi.report.model.bo.LogisticsSettlementBO;
import com.ssy.lingxi.report.model.bo.StatisticsCountBO;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import java.util.List;

/**
 * 物流订单DAO
 * @author 伍将
 * @date 2020/11/13
 * @version 2.0.0
 */
@Service
public class LogisticsOrderDAO {

    @Autowired
    @Qualifier("logisticsJdbcTemplate")
    private JdbcTemplate jdbcTemplate;

    /**
     * 查询物流统计数量
     * @return
     */
    public List<StatisticsCountBO> getStatisticsCount() {
        StringBuilder sql = new StringBuilder();
        sql.append(" select ")
                .append(" c.company_member_id member_id,c.company_role_id role_id,o.status status_type,count(c.id)")
                .append(" from ls_logistics_order o")
                .append(" inner join ls_logistics_company c")
                .append(" on o.company_id=c.id")
                .append(" where o.status =2")  // 待确认
                .append(" and c.cooperate_type=1")   // 平台物流服务商
                .append(" group by c.company_member_id,c.company_role_id,o.status")
                .append(" union")
                .append(" select ")
                .append(" member_id,role_id,status status_type,count(id)")
                .append(" FROM ")
                .append(" ls_logistics_order ")
                .append(" where status =1")  // 待提交
                .append(" group by member_id,role_id,status;");

        return jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(StatisticsCountBO.class));
    }

    /**
     * 获取物流结算数据
     * @author 伍将
     * @date 2020/11/27
     * @param startTime: 开始时间
     * @param endTime: 结束时间
     * @param payMemberId: 支付会员id（物流单发起者）
     * @param payRoleId: 支付会员角色id（物流单发起者）
     * @param memberId: 收款会员id（平台物流会员）
     * @param roleId: 收款会员角色id（平台物流会员）
     * @return:
     **/
    public LogisticsSettlementBO getSettlement(Long startTime, Long endTime, Long payMemberId, Long payRoleId, Long memberId, Long roleId) {
        StringBuilder sql = new StringBuilder();
        sql.append(" select ")
                .append(" count(o.id) total_count,COALESCE(sum(o.freight_price),0) total_amount")
                .append(" from ls_logistics_order o")
                .append(" inner join ls_logistics_company c")
                .append(" on o.company_id=c.id")
                .append(" where o.status =4")  // 接受物流
                .append(" and c.cooperate_type=1")   // 平台物流服务商
                .append(" and confirm_time between " + startTime + " and " + endTime)
                .append(" and o.member_id=" + payMemberId + " and o.role_id=" + payRoleId)
                .append(" and c.company_member_id=" + memberId + " and c.company_role_id=" + roleId + " ;");

        List<LogisticsSettlementBO> resultList = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(LogisticsSettlementBO.class));

        // 剔除订单数与运费总额全为0的数据
        return resultList.stream().filter(o -> o.getTotalCount() > 0 || o.getTotalAmount() > 0).findFirst().orElse(null);
    }

    /**
     * 获取物流结算明细
     * @param startTime
     * @param endTime
     * @param payMemberId
     * @param payRoleId
     * @param memberId
     * @param roleId
     * @return
     */
    public List<MemberLogisticsSettlementDO> getSettlementDetail(Long startTime, Long endTime, Long payMemberId, Long payRoleId, Long memberId, Long roleId) {
        StringBuilder sql = new StringBuilder();
        sql.append(" select ")
                .append(" COALESCE(o.logistics_order_no,'-') order_no,o.digest order_abstract,o.create_time order_time,o.total_carton,o.total_weight,")
                .append(" o.total_volume,COALESCE(o.freight_price,0) order_amount,o.confirm_time accept_order_time,o.id dataId,")
                .append(" coalesce(o.tax_inclusive,0) is_has_tax,coalesce(o.tax_rate,0) tax_rate")
                .append(" from ls_logistics_order o")
                .append(" inner join ls_logistics_company c")
                .append(" on o.company_id=c.id")
                .append(" where o.status =4")  // 接受物流
                .append(" and c.cooperate_type=1")   // 平台物流服务商
                .append(" and confirm_time between " + startTime + " and " + endTime)
                .append(" and o.member_id=" + payMemberId + " and o.role_id=" + payRoleId)
                .append(" and c.company_member_id=" + memberId + " and c.company_role_id=" + roleId + " ;");

        return jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(MemberLogisticsSettlementDO.class));
    }

    /**
     * 插入结算明细
     * @param settlementId
     * @param startTime
     * @param endTime
     * @param payMemberId
     * @param payRoleId
     * @param memberId
     * @param roleId
     */
    public void insertSettlementDetail(Long settlementId, Long startTime, Long endTime, Long payMemberId, Long payRoleId, Long memberId, Long roleId) {
        StringBuilder sql = new StringBuilder();
        sql.append(" insert into sa_member_logistics_settlement")
                .append("(id,settlement_id,order_no,order_abstract,order_time,o.total_carton,o.total_weight,o.total_volume,order_amount,accept_order_time,settlement_amount,member_id,role_id,pay_member_id,pay_role_id)")
                .append(" select nextval('sa_member_logistics_settlement_seq')," + settlementId + ",")
                .append(" o.invoices_no,o.digest,o.create_time,o.total_carton,o.total_weight,")
                .append(" o.total_volume,COALESCE(o.freight_price,0),o.confirm_time,COALESCE(o.freight_price,0)")
                .append("," + memberId + "," + roleId + "," + payMemberId + "," + payRoleId + ")")
                .append(" from ls_logistics_order o")
                .append(" inner join ls_logistics_company c")
                .append(" on o.company_id=c.id")
                .append(" where o.status =4")  // 接受物流
                .append(" and c.cooperate_type=1")   // 平台物流服务商
                .append(" and confirm_time between " + startTime + " and " + endTime)
                .append(" and o.member_id=" + payMemberId + " and o.role_id=" + payRoleId)
                .append(" and c.member_id=" + memberId + " and c.role_id=" + roleId + " ;");

        jdbcTemplate.execute(sql.toString());
    }
}
